package com.tju.resource.result;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import com.tju.resource.tool.tool.ValueUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;
import java.util.function.Function;

/**
 * @Author HyEISN
 * generate simple excel(.xls) template file's result, now support .xls only
 */
public class SimpleXls {
    private static final String PARAM_REG = "\\{::\\w+\\}";

    private static final String COLUMN_REG = "\\{r::\\w+\\}";

    /**
     * @param template excel template file
     * @param data     fill data, supper one list
     * @param saveTo   save target excel path, full path
     */
    public static void fillTemplate(String template, JSONObject data, String saveTo) {
        try {
            fillTemplate(template, data, FileUtil.getOutputStream(saveTo));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param template     excel template file
     * @param data         fill data, supper one list
     * @param outputStream output to
     */
    public static void fillTemplate(String template, JSONObject data, OutputStream outputStream) {
        Workbook workbook = toWorkBook(template);

        try {
            if (null != workbook) {
                Iterator<Sheet> iterator = workbook.sheetIterator();
                Sheet sheet = null;

                while (iterator.hasNext()) {
                    Sheet next = iterator.next();

                    if (StringUtils.equals(next.getSheetName(), "Sheet1")) {
                        sheet = next;
                    }
                }

                if (null != sheet) {
                    fillTempData(sheet, data);
                    workbook.write(outputStream);
                    outputStream.flush();
                    outputStream.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @param template     excel template file
     * @param data         fill data, supper one list
     * @param outputStream output to
     */
    public static void fillTemplate(String template, InputStream inputStream, JSONObject data, OutputStream outputStream) {
        Workbook workbook = toWorkBook(template, inputStream);

        try {
            if (null != workbook) {
                Iterator<Sheet> iterator = workbook.sheetIterator();
                Sheet sheet = null;

                while (iterator.hasNext()) {
                    Sheet next = iterator.next();

                    if (StringUtils.equals(next.getSheetName(), "Sheet1")) {
                        sheet = next;
                    }
                }

                if (null != sheet) {
                    fillTempData(sheet, data);
                    workbook.write(outputStream);
                    outputStream.flush();
                    outputStream.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static void fillTempData(Sheet sheet, JSONObject data) {
        int rows = sheet.getLastRowNum() + 1;
        Row cro = null;
        Row curo = null;
        List<Row> fRows = new ArrayList<>();
        JSONArray list = data.getJSONArray("list");
        boolean log = false;
        int sfStart = -1;
        int sfEnd = -1;

        for (int i = 0; i < rows; i++) {
            Row row = sheet.getRow(i);
            Iterator<Cell> cellIter = row.cellIterator();

            while (cellIter.hasNext()) {
                Cell cell = cellIter.next();
                String cellValue = cell.getStringCellValue();
                List<String> paraReg = ReUtil.findAll(PARAM_REG, cellValue, 0);
                List<String> columnReg = ReUtil.findAll(COLUMN_REG, cellValue, 0);

                if (null != paraReg && 0 != paraReg.size()) {
                    for (String rex : paraReg) {
                        String key = rex
                                .replace("{", "")
                                .replace("}", "")
                                .replace("::", "");
                        String value = data.getStr(key);

                        cellValue = cellValue.replace(rex, ValueUtil.strOrEmp(value));
                    }
                    cell.setCellValue(cellValue);
                }

                if (null != columnReg && 0 != columnReg.size()) {
                    if (null == cro) {
                        cro = row;
                    }

                    if (curo != row) {
                        fRows.add(row);
                        curo = row;
                    }
                }
            }

            if (null != cro) {
                if (!log) {
                    log = true;
                } else {
                    if (sfStart == -1) sfStart = row.getRowNum();
                    if (sfEnd == -1) sfEnd = row.getRowNum();
                    sfEnd++;
                }
            }
        }

        if (null != cro) {
            sheet.shiftRows(sfStart + (fRows.size() - 1), sfEnd, (fRows.size() * (list.size() - 1)), true, true);
            genRowsData(genRowsAndCells(sheet, fRows, list.size()), list, fRows.size());
        }
    }

    /**
     * parse to workbook
     */
    public static Workbook toWorkBook(String excelPath) {
        Workbook wb = null;
        BufferedInputStream fis = null;
        File f = new File(excelPath);
        String[] split = excelPath.split("\\.");
        boolean xls = StringUtils.equals(split[split.length - 1], "xls");
        boolean xlsx = StringUtils.equals(split[split.length - 1], "xlsx");

        try {
            fis = FileUtil.getInputStream(f);

            if (xls) {
                wb = new HSSFWorkbook(fis);
            } else if (xlsx) {
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    /**
     * parse to workbook
     */
    public static Workbook toWorkBook(String excelPath, InputStream inputStream) {
        Workbook wb = null;
        BufferedInputStream fis = null;
        String[] split = excelPath.split("\\.");
        boolean xls = StringUtils.equals(split[split.length - 1], "xls");
        boolean xlsx = StringUtils.equals(split[split.length - 1], "xlsx");

        try {
            fis = new BufferedInputStream(inputStream);

            if (xls) {
                wb = new HSSFWorkbook(fis);
            } else if (xlsx) {
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    /**
     * generate rows and cells
     *
     * @return added rows
     */
    private static List<Row> genRowsAndCells(Sheet sheet, List<Row> srcRow, int count) {
        List<Row> rows = new ArrayList<>(srcRow);
        int curRow = 0;
        int lastOn = srcRow.get(srcRow.size() - 1).getRowNum();
        int lastRo = lastOn + (srcRow.size() * (count - 1));

        for (int i = lastOn + 1; i <= lastRo; i++) {
            Row row = sheet.createRow(i);
            Row r = srcRow.get(curRow);
            Iterator<Cell> rIter = r.cellIterator();

            while (rIter.hasNext()) {
                Cell rNext = rIter.next();
                Cell rowCell = row.createCell(rNext.getColumnIndex());
                int[] cellInMerge = getCellInMerge(sheet, rNext);

                if (null != cellInMerge) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(i, i, cellInMerge[0], cellInMerge[1]));
                }

                rowCell.setCellStyle(rNext.getCellStyle());
                rowCell.setCellValue(rNext.getStringCellValue());
            }
            row.setHeightInPoints(r.getHeightInPoints());
            if (null != r.getRowStyle()) row.setRowStyle(r.getRowStyle());

            rows.add(row);
            curRow++;
            if (curRow > srcRow.size() - 1) {
                curRow = 0;
            }
        }

        return rows;
    }

    /**
     * generate rows values
     */
    private static void genRowsData(List<Row> rows, JSONArray data, int dataRow) {
        int rowIdx = 0;

        for (Object datum : data) {
            JSONObject rowData = JSONUtil.parseObj(datum);

            for (int i2 = 0; i2 < dataRow; i2++) {
                Row row = rows.get(rowIdx);
                Iterator<Cell> cellIter = row.cellIterator();

                while (cellIter.hasNext()) {
                    Cell next = cellIter.next();
                    List<String> columnReg = ReUtil.findAll(COLUMN_REG, next.getStringCellValue(), 0);
                    String colValue = next.getStringCellValue();

                    if (!StringUtils.isBlank(colValue)) {
                        for (String reg : columnReg) {
                            String key = reg
                                    .replace("{", "")
                                    .replace("}", "")
                                    .replace("r::", "");
                            String val = rowData.getStr(key);

                            colValue = colValue.replace(reg, ValueUtil.strOrEmp(val));
                        }
                        next.setCellValue(colValue);
                    }
                }

                rowIdx++;
            }
        }
    }

    /**
     * Get merge cell in sheet if cell in merge columns.
     *
     * @return if cell in merge column 0 - first column, 1 - last column, or null
     */
    private static int[] getCellInMerge(Sheet sheet, Cell cell) {
        int[] res = null;

        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(cell)) {
                res = new int[]{region.getFirstColumn(), region.getLastColumn()};
            }
        }

        return res;
    }

    /**
     * load excel data to java bean.
     *
     * @param beanType
     * @param keyToBean fields name in bean, mapping to excel header, use like: put("客户名称", "clientName")
     * @param in        excel InputStream
     * @return bean list
     */
    public static <T> List<T> readAsBeans(Class<T> beanType, final Map<String, String> keyToBean
            , InputStream in) {
        List<Map<String, Object>> rows = ExcelUtil.getReader(in).readAll();
        List<T> ret = new ArrayList<>();

        rows.forEach(row -> {
            JSONObject json = new JSONObject(row);
            T beanIns = null;

            try {
                beanIns = beanType.getConstructor().newInstance();
            } catch (Exception e) {
                e.printStackTrace();
            }

            for (String key : json.keySet()) {
                String field = keyToBean.get(key);
                String setter = "set" + StrUtil.upperFirst(field);

                if (null != field) {
                    try {
                        Class<?> type = beanType.getDeclaredField(field).getType();
                        Method setterMth = beanType.getMethod(setter, type);

                        setterMth.invoke(beanIns, json.get(key, type));
                    } catch (NoSuchFieldException e) {
                        System.err.println(String.format("Inject bean WARNING: field %s.%s not exists.", beanType.getTypeName(), field));
                    } catch (NoSuchMethodException me) {
                        System.err.println(String.format("Inject bean WARNING: method %s.%s not exists.", beanType.getTypeName(), setter));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                } else {
                    if (null != beanType) {
                        System.err.printf("Inject bean WARNING: column (%s) %s is not mapping in keyToBean.\n", beanType.getTypeName(), key);
                    }
                }
            }

            ret.add(beanIns);
        });

        return ret;
    }

    /**
     * load excel data to java bean.
     *
     * @param beanType
     * @param keyToBean fields name in bean, mapping to excel header, use like: put("客户名称", "clientName")
     * @param in        excel InputStream
     * @param forEach   foreach dates
     * @return bean list
     */
    public static <T> List<T> readAsBeans(Class<T> beanType, final Map<String, String> keyToBean
            , InputStream in, Consumer<T> forEach) {
        List<T> ts = readAsBeans(beanType, keyToBean, in);

        ts.forEach(forEach);
        return ts;
    }

    /**
     * load excel to java bean and save them
     *
     * @param beanType
     * @param keyToBean fields name in bean, mapping to excel header, use like: put("客户名称", "clientName")
     * @param in        excel InputStream
     * @param operation save action
     * @return add result.
     */
    public static <T> Result<Object> saveAsBeans(Class<T> beanType, final Map<String, String> keyToBean
            , InputStream in, Function<T, Result<Object>> operation) {
        List<T> beans = SimpleXls.readAsBeans(beanType, keyToBean, in);

        return operateBeans(beans, operation);
    }

    /**
     * load excel to java bean and save them
     *
     * @param beanType
     * @param keyToBean fields name in bean, mapping to excel header, use like: put("客户名称", "clientName")
     * @param in        excel InputStream
     * @param operation save action
     * @param forEach   foreach dates
     * @return add result.
     */
    public static <T> Result<Object> saveAsBeans(Class<T> beanType, final Map<String, String> keyToBean
            , InputStream in, Function<T, Result<Object>> operation, Consumer<T> forEach) {
        List<T> beans = SimpleXls.readAsBeans(beanType, keyToBean, in, forEach);

        return operateBeans(beans, operation);
    }

    private static <T> Result<Object> operateBeans (List<T> beans, Function<T, Result<Object>> operation) {
        StringBuilder result = new StringBuilder();
        AtomicInteger i = new AtomicInteger(2);
        AtomicInteger doneCnt = new AtomicInteger();
        AtomicInteger failCnt = new AtomicInteger();

        beans.forEach(tbClient -> {
            Result<Object> objectResult = operation.apply(tbClient);

            if (objectResult.getCode().equals(ResultEnum.UNKONW_ERROR.getCode())) {
                result.append("在第 ").append(i.get()).append(" 行: ").append(objectResult.getMsg()).append("\n");
                failCnt.incrementAndGet();
            } else if (objectResult.getCode().equals(ResultEnum.SUCCESS.getCode())) {
                doneCnt.getAndIncrement();
            }

            i.getAndIncrement();
        });

        if (doneCnt.get() == 0) {
            return ResultUtil.error(ResultEnum.UNKONW_ERROR, "导入失败,原因:\n" + result);
        } else {
            Result<Object> success = ResultUtil.success(ResultEnum.SUCCESS, "");

            if (result.length() > 0) {
                success.setMsg(String.format("已导入 %d 条数据,有 %d 条数据导入失败,原因:\n", doneCnt.get(), failCnt.get()) + result.toString());
            } else {
                success.setMsg(String.format("导入成功! 共导入 %d 条数据.", doneCnt.get()));
            }

            return success;
        }
    }
}
